package com.arges.web.dao;

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.Dictionary;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.FlushMode;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate4.HibernateTemplate;
import org.springframework.orm.hibernate4.SessionFactoryUtils;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.hibernate.SimpleHibernateTemplate;

import com.arges.web.common.Constant;
import com.arges.web.common.GenericsUtils;
import com.arges.web.common.PropertiesUtils;
import com.arges.web.common.QueryColumn;
import com.arges.web.common.QueryMixture;
import com.arges.web.domain.tableutil.DbTableForDataDelete;
import com.arges.web.domain.tableutil.EnhancedReflectionUtils;
import com.arges.web.domain.tableutil.JoinedTable;
import com.arges.web.page.PageInfo;

/**
 * Dao层公用类
 * 
 * @author arges
 * 
 * @param <T>
 * @param <PK>
 */
@SuppressWarnings("unchecked")
@Transactional
public class GenericDao<T, PK extends Serializable>
{
	private Class<T> entryClass;

	private SimpleHibernateTemplate<T, PK> simpleHibernateTemplate = null;

	@Autowired
	protected HibernateTemplate hibernateTemplate;

	@Autowired
	private PaginationJdbcSupport<T> paginationJdbcSupport;

	@Autowired
	private SessionFactory sessionFactory;

	public GenericDao()
	{
		this.entryClass = GenericsUtils.getSuperClassGenricType(getClass());
	}

	public GenericDao(Class<T> entryClass, SessionFactory sessionFactory)
	{
		super();
		this.entryClass = entryClass;
		this.sessionFactory = sessionFactory;
		this.simpleHibernateTemplate = new SimpleHibernateTemplate<T, PK>(sessionFactory, entryClass);
	}

	/**
	 * 打开session
	 * 
	 * @return
	 */
	public Session getSession()
	{
		return sessionFactory.getCurrentSession();
	}

	/**
	 * 返回hibernateTemplate
	 * 
	 * @return
	 */
	public HibernateTemplate getHibernateTemplate()
	{
		return hibernateTemplate;
	}

	/**
	 * 保存某个对象
	 * 
	 * @param object
	 */
	public void save(T object)
	{
		getSession().save(object);
	}

	/**
	 * 删除某个对象
	 * 
	 * @param object
	 */
	public void delete(T object)
	{
		getSession().delete(object);
	}

	/**
	 * 根据id删除某个对象
	 * 
	 * @param object
	 */
	public void delete(PK id)
	{
		if (null != get(id))
		{
			getSession().delete(get(id));
		}
	}

	/**
	 * 保存或者更新某个对象
	 * 
	 * @param object
	 */
	public void saveOrUpdate(T object)
	{
		getSession().saveOrUpdate(object);
	}

	/**
	 * 更新某个对象
	 * 
	 * @param object
	 */
	public void update(T object)
	{
		getSession().update(object);
	}

	/**
	 * 根据id查找某个对象
	 * 
	 * @param 主键id
	 * @return T
	 */
	public T get(PK id)
	{
		return (T) getSession().get(entryClass, id);
	}

	/**
	 * 查找所有对象
	 */
	public List<T> findAll()
	{
		return getSession().createCriteria(entryClass).list();
	}

	/**
	 * 批量保存数据
	 * 
	 * @param collection 数据集合
	 */
	public void batchSave(Collection<? extends Object> collection)
	{
		Session session = getSession();
		if (null == collection)
		{
			return;
		}
		// 插入总数据条数
		int allNum = collection.size();
		// 每次插入25条记录最后剩余数据条数
		int restNum = allNum % 25;
		// 总共插入次数
		int flushNum = allNum / 25;
		Iterator<? extends Object> iterator = collection.iterator();
		// 记录插入的数据数
		int index = 0;
		while (iterator.hasNext())
		{
			Object object = iterator.next();
			session.save(object);
			++index;
			if (index <= flushNum * 25)
			{
				if (index % 25 == 0)
				{
					session.flush();
					session.clear();
				}
				else if (restNum > 0 && index == allNum)
				{
					session.flush();
					session.clear();
				}
			}
		}
	}

	/**
	 * 批量保存或者更新数据
	 * 
	 * @param collection 数据集合
	 */
	public void batchSaveOrUpdate(Collection<? extends Object> collection)
	{
		Session session = getSession();
		if (null == collection)
		{
			return;
		}
		// 插入总数据条数
		int allNum = collection.size();
		// 每次插入25条记录最后剩余数据条数
		int restNum = allNum % 25;
		// 总共插入次数
		int flushNum = allNum / 25;
		Iterator<? extends Object> iterator = collection.iterator();
		// 记录插入的数据数
		int index = 0;
		while (iterator.hasNext())
		{
			Object object = iterator.next();
			session.saveOrUpdate(object);
			++index;
			if (index <= flushNum * 25)
			{
				if (index % 25 == 0)
				{
					session.flush();
					session.clear();
				}
				else if (restNum > 0 && index == allNum)
				{
					session.flush();
					session.clear();
				}
			}
		}
	}

	/**
	 * 根据sql语句查询实体集合
	 * 
	 * @param clazz
	 * @param sql
	 * @param values
	 * @return
	 */
	public List<T> findAllSql(Class<T> clazz, String sql, Object... values)
	{
		return paginationJdbcSupport.getResult(clazz, sql, values);
	}

	/**
	 * 通过sql语句查询总条数
	 * 
	 * @param sql
	 * @param values 条件
	 * @return
	 */
	public long countSqlQueryResult(String sql, Object... values)
	{
		// 移除
		sql = sql.substring(sql.toUpperCase().indexOf("FROM") + 4, sql.length());
		StringBuffer stringBuffer = new StringBuffer("SELECT count(*) FROM (");
		stringBuffer.append(sql);
		stringBuffer.append(" )");
		Query query = getSession().createSQLQuery(stringBuffer.toString());
		if (null != values && values.length > 0)
		{
			setQueryValue(query, values);
		}
		return (Long) query.uniqueResult();
	}

	/**
	 * 通过hql语句查询总条数
	 * 
	 * @param sql
	 * @param values 条件
	 * @return
	 */
	public long countQueryResult(String hql, Object... values)
	{
		hql = hql.substring(hql.toUpperCase().indexOf("FROM") + 4, hql.length());
		StringBuffer stringBuffer = new StringBuffer(" SELECT count(*) FROM (");
		stringBuffer.append(hql);
		stringBuffer.append(" )");
		Query query = getSession().createQuery(stringBuffer.toString());
		if (null != values && values.length > 0)
		{
			setQueryValue(query, values);
		}
		return (Long) query.uniqueResult();
	}

	/**
	 * 通过criteria语句查询总条数
	 * 
	 * @param sql
	 * @param values 条件
	 * @return
	 */
	public long countCriteriaResult(Class<T> clazz, Criterion criterion)
	{
		Criteria criteria = getSession().createCriteria(clazz);
		if (null != criterion)
		{
			criteria.add(criterion);
		}
		return (Long) criteria.setProjection(Projections.rowCount()).uniqueResult();
	}

	/**
	 * 通过criteria语句查询总条数
	 * 
	 * @param sql
	 * @param values 条件
	 * @return
	 */
	public long countCriteriaResult(Criteria criteria)
	{
		return (Long) criteria.setProjection(Projections.rowCount()).uniqueResult();
	}

	/**
	 * 设置查询条件参数
	 * 
	 * @param query
	 * @param values
	 */
	public void setQueryValue(Query query, Object... values)
	{
		for (int i = 0; i < values.length; i++)
		{
			query.setParameter(i, values[i]);
		}
	}

	/**
	 * 分页查询实体类对象
	 * 
	 * @param page
	 * @param hql
	 * @param values
	 * @return
	 */
	public PageInfo<T> findAll(PageInfo<T> page, String hql, Object... values)
	{
		long totalCount = countQueryResult(hql, values);
		page.setTotalCount(totalCount);
		Query createQuery = getSession().createQuery(hql);
		if (null != values)
		{
			setQueryValue(createQuery, values);
		}
		List<T> list = createQuery.setFirstResult(page.getFirst() - 1).setMaxResults(page.getPageSize()).list();
		page.setResult(list);
		return page;
	}

	/**
	 * 分页查询实体类对象
	 * 
	 * @param page
	 * @param hql
	 * @param values
	 * @return
	 */
	public PageInfo<T> findAll(PageInfo<T> page, Class<T> clazz, Criterion criterion)
	{
		long totalCount = countCriteriaResult(clazz, criterion);
		Criteria criteria = getSession().createCriteria(clazz);
		if (null != criterion)
		{
			criteria.add(criterion);
		}
		if (null != page.getOrderByName())
		{
			if (Constant.SORTORDER.equals(page.getOrder()))
			{
				criteria.addOrder(Order.asc(page.getOrderByName()));
			}
			else
			{
				criteria.addOrder(Order.desc(page.getOrderByName()));
			}
		}
		else
		{
			if (Constant.SORTORDER.equals(page.getOrder()))
			{
				criteria.addOrder(Order.asc("id"));
			}
			else
			{
				criteria.addOrder(Order.desc("id"));
			}
		}
		List<T> result = criteria.setFirstResult(page.getFirst() - 1).setMaxResults(page.getPageSize()).list();
		page.setTotalCount(totalCount);
		page.setResult(result);
		return page;
	}

	/**
	 * 分页查询实体类对象(包含对实体内某个实体属性的模糊查询)
	 * 
	 * @param page
	 * @param hql
	 * @param values
	 * @return
	 */
	public PageInfo<T> findAll(PageInfo<T> page, Class<T> clazz, Criterion criterion, String searchValue,
			String property, String... entryName)
	{
		long totalCount = countCriteriaResult(clazz, criterion, searchValue, property, entryName);
		Criteria criteria = getSession().createCriteria(clazz);
		if (null != criterion)
		{
			criteria.add(criterion);
		}
		for (String string : entryName)
		{
			criteria.createCriteria(string).add(Restrictions.like(property, searchValue, MatchMode.ANYWHERE));
		}
		if (null != page.getOrderByName())
		{
			if (Constant.SORTORDER.equals(page.getOrder()))
			{
				criteria.addOrder(Order.asc(page.getOrderByName()));
			}
			else
			{
				criteria.addOrder(Order.desc(page.getOrderByName()));
			}
		}
		else
		{
			if (Constant.SORTORDER.equals(page.getOrder()))
			{
				criteria.addOrder(Order.asc("id"));
			}
			else
			{
				criteria.addOrder(Order.desc("id"));
			}
		}
		List<T> result = criteria.setFirstResult(page.getFirst() - 1).setMaxResults(page.getPageSize()).list();
		page.setTotalCount(totalCount);
		page.setResult(result);
		return page;
	}

	/**
	 * 通过criteria语句查询总条数
	 * 
	 * @param sql
	 * @param values 条件
	 * @return
	 */
	public long countCriteriaResult(Class<T> clazz, Criterion criterion, String searchValue, String property,
			String... entryName)
	{
		Criteria criteria = getSession().createCriteria(clazz);
		if (null != criterion)
		{
			criteria.add(criterion);
		}
		// if (entryName.length == 1)
		// {
		// criteria.createCriteria(entryName[0]).add(Restrictions.like(property, searchValue, MatchMode.ANYWHERE));
		// }
		// else
		// {
		// Criterion searchName = null;
		for (String string : entryName)
		{
			criteria.createCriteria(string).add(Restrictions.like(property, searchValue, MatchMode.ANYWHERE));

		}

		// }
		return (Long) criteria.setProjection(Projections.rowCount()).uniqueResult();
	}

	/**
	 * 根据id批量删除
	 * 
	 * @param ids
	 */
	public void batchDeleteByIds(String hql, Long[] ids)
	{
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(hql).append("(");
		for (int i = 0; i < ids.length; i++)
		{
			stringBuffer.append(ids[i] + ",");
		}
		stringBuffer.deleteCharAt(stringBuffer.length() - 1).append(")");
		getSession().createQuery(stringBuffer.toString()).executeUpdate();
	}

	/**
	 * 
	 * TODO 将其他 findByConditions方法并入到该方法内
	 *
	 * @param queryMixture
	 * @return
	 * @since Ver 3.0
	 */
	@SuppressWarnings("unchecked")
	public List findByQueryMixture(QueryMixture queryMixture)
	{
		List result = null;
		try
		{
			DetachedCriteria criteria = createDetachedCriterion(queryMixture);
			if (queryMixture.getSize() != null && queryMixture.getStartIndex() != null)
			{
				result = hibernateTemplate.findByCriteria(criteria, queryMixture.getStartIndex(),
						queryMixture.getSize());
			}
			else
			{
				result = hibernateTemplate.findByCriteria(criteria);
			}
		}
		catch (RuntimeException e)
		{
			throw e;
		}
		return result;
	}

	@SuppressWarnings("unchecked")
	private DetachedCriteria createDetachedCriterion(QueryMixture queryMixture)
	{
		DetachedCriteria rootCriteria = DetachedCriteria.forClass(queryMixture.getPojoClass());
		Map<String, DetachedCriteria> criterias = new HashMap<String, DetachedCriteria>();
		criterias.put("", rootCriteria);

		if (queryMixture.getJoinedTables() != null)
		{
			for (JoinedTable joinedTable : queryMixture.getJoinedTables())
			{
				String aliasName = joinedTable.getAliasName();
				if (!criterias.containsKey(aliasName))
				{
					if (aliasName.contains("."))
					{
						String[] aliasNames = aliasName.split("\\.");
						String firstAliasName = aliasNames[0];
						String secondAliasName = aliasNames[1];

						if (!criterias.containsKey(firstAliasName))
						{
							criterias
									.put(firstAliasName,
											rootCriteria.createAlias(firstAliasName, firstAliasName,
													joinedTable.getJoinType()));
						}

						criterias.put(aliasName,
								rootCriteria.createAlias(aliasName, secondAliasName, joinedTable.getJoinType()));
					}
					else
					{
						criterias.put(aliasName,
								rootCriteria.createAlias(aliasName, aliasName, joinedTable.getJoinType()));
					}
				}
			}
		}

		if (queryMixture.getConditions() != null)
		{
			for (Criterion criterion : queryMixture.getConditions())
			{
				rootCriteria.add(criterion);
			}
		}

		ProjectionList projectionList = null;

		if (queryMixture.getProjections() != null)
		{
			projectionList = createProjectionListIfNeed(rootCriteria, projectionList);
			for (Projection projection : queryMixture.getProjections())
			{
				projectionList.add(projection);
			}
		}

		if (queryMixture.getQueryCols() != null)
		{
			projectionList = createProjectionListIfNeed(rootCriteria, projectionList);
			generateProjections(queryMixture.getQueryCols(), criterias, projectionList);
			if (queryMixture.isToObj())
			{
				Class clazz = queryMixture.getResultClass() == null ? queryMixture.getPojoClass() : queryMixture
						.getResultClass();
				rootCriteria.setResultTransformer(Transformers.aliasToBean(clazz));
			}
		}

		if (queryMixture.getOrders() != null)
		{
			for (Order order : queryMixture.getOrders())
			{
				rootCriteria.addOrder(order);
			}
		}
		return rootCriteria;
	}

	private ProjectionList createProjectionListIfNeed(DetachedCriteria rootCriteria, ProjectionList projectionList)
	{
		if (projectionList == null)
		{
			projectionList = Projections.projectionList();
			rootCriteria.setProjection(projectionList);
		}
		return projectionList;
	}

	/*
	 * 名词解释，QueryColumn中 propertyName 内相应的名词解释, 以下例子均以 propertyName = "device.organization.orgName"为条件 prefix: 前缀，即
	 * device.organization lastPrefix: 最后的一个前缀，即 organization propertyName: 属性名，即 orgName
	 */
	private ProjectionList generateProjections(List<QueryColumn> queryCols, Map<String, DetachedCriteria> criterias,
			ProjectionList projectionList)
	{
		for (QueryColumn queryCol : queryCols)
		{
			String propertyAlias = queryCol.getPropertyAlias();
			if (queryCol.getPropertyName().contains("."))
			{
				String[] cols = queryCol.getPropertyName().split("\\.");
				StringBuilder colPrefix = new StringBuilder();

				final int LAST_INDEX = cols.length - 1;
				final int LAST_PREFIX_INDEX = LAST_INDEX - 1;

				String prefix = null;
				for (int i = 0; i < LAST_INDEX; i++)
				{
					if (i != 0)
					{
						colPrefix.append(".");
					}

					colPrefix.append(cols[i]);

					prefix = colPrefix.toString();
				}

				String propertyName = cols[LAST_INDEX];
				String lastPrefix = cols[LAST_PREFIX_INDEX];
				String propertyNameWithLastPrefix = lastPrefix + "." + propertyName;

				if (!criterias.containsKey(prefix))
				{
					criterias.put(prefix, criterias.get("").createAlias(prefix, lastPrefix));
				}

				if (propertyAlias == null)
				{
					propertyAlias = propertyName;
				}

				projectionList.add(Projections.property(propertyNameWithLastPrefix), propertyAlias);
			}
			else
			{
				if (propertyAlias == null)
				{
					propertyAlias = queryCol.getPropertyName();
				}
				projectionList.add(Projections.property(queryCol.getPropertyName()), propertyAlias);
			}
		}

		return projectionList;
	}

	public List findByConditions(List<Criterion> conditions, Class<?> pojoClass, List<QueryColumn> queryCols)
	{
		return hibernateTemplate.findByCriteria(createDetachedCriterion(conditions, pojoClass, null, queryCols));
	}

	/*
	 * TODO 目前仅支持2层 别名
	 */
	private DetachedCriteria createDetachedCriterion(List<Criterion> conditions, Class<?> pojoClass,
			List<JoinedTable> joinedTables, List<QueryColumn> queryCols)
	{
		return createDetachedCriterion(conditions, pojoClass, joinedTables, queryCols, true, null);
	}

	private DetachedCriteria createDetachedCriterion(List<Criterion> conditions, Class<?> pojoClass,
			List<JoinedTable> joinedTables, List<QueryColumn> queryCols, boolean toObj, List<Order> orders)
	{
		return createDetachedCriterion(conditions, pojoClass, joinedTables, queryCols, toObj, null, orders);
	}

	/*
	 * TODO 目前仅支持2层 别名, 如 EncoderChannel.java内，可查询 relation.device的属性，不可查询relation.device.organization的属性
	 * 
	 * @param toObj 只有在queryCols有值的时候才会用到该属性，表示是否将查询结果转化为pojo对象
	 */
	@SuppressWarnings("unchecked")
	private DetachedCriteria createDetachedCriterion(List<Criterion> conditions, Class pojoClass,
			List<JoinedTable> joinedTables, List<QueryColumn> queryCols, boolean toObj, Class resultClass,
			List<Order> orders)
	{
		QueryMixture queryMixture = new QueryMixture();
		queryMixture.setConditions(conditions);
		queryMixture.setPojoClass(pojoClass);
		queryMixture.setJoinedTables(joinedTables);
		queryMixture.setQueryCols(queryCols);
		queryMixture.setToObj(toObj);
		queryMixture.setResultClass(resultClass);
		queryMixture.setOrders(orders);
		return createDetachedCriterion(queryMixture);
	}

	@SuppressWarnings("unchecked")
	public Long getTotalCount(List<Criterion> conditions, Class pojoClass, List<JoinedTable> joinedTables)
	{
		QueryMixture queryMixture = new QueryMixture(QueryMixture.asProjectionList(Projections.rowCount()), conditions,
				pojoClass, joinedTables);
		return (Long) findSingleRecordByQueryMixture(queryMixture);
	}

	public Object findSingleRecordByQueryMixture(QueryMixture queryMixture)
	{
		return getFirstRecordOrNull(findByQueryMixture(queryMixture));
	}

	public Object findSingleRecordByCondtion(Criterion condition, Class<?> pojoClass)
	{
		return getFirstRecordOrNull(findByCondition(condition, pojoClass));
	}

	/**
	 * 如果list内存在对象，则返回第一条记录，否则返回null
	 */
	protected Object getFirstRecordOrNull(List<?> pojoList)
	{
		Object result = null;
		if (pojoList.size() != 0)
		{
			result = pojoList.get(0);
		}
		return result;
	}

	public List<Dictionary> getDictionaryList(String dictionaryType, String param1, String param2)
	{
		List<Criterion> conditions = new ArrayList<Criterion>();
		if (StringUtils.isNotBlank(param1) && !"null".equals(param1))
		{
			conditions.add(Restrictions.eq("param1", param1));
		}

		if (StringUtils.isNotBlank(param2) && !"null".equals(param2))
		{
			conditions.add(Restrictions.eq("param2", param2));
		}

		return getDictionaryList(dictionaryType, conditions);
	}

	@SuppressWarnings("unchecked")
	public List<Dictionary> getDictionaryList(String dictionaryType, List<Criterion> conditions)
	{
		if (!PropertiesUtils.isConfigOn(Constant.IGNORE_PROJECT_TYPE))
		{
			conditions.add(Restrictions.eq("status", Constant.DB_TRUE));
		}
		conditions.add(Restrictions.eq("type.status", Constant.DB_TRUE));
		conditions.add(Restrictions.eq("type.code", dictionaryType));
		return getDataListWithCache(conditions, Order.asc("sort"), Dictionary.class, JoinedTable.asList("type"));
	}

	public List getDataListWithCache(List<Criterion> conditions, Order order, Class pojoClass,
			List<JoinedTable> joinedTables)
	{
		final DetachedCriteria detachedCriteria = createDetachedCriterion(conditions, pojoClass, joinedTables, null);
		if (order != null)
		{
			detachedCriteria.addOrder(order);
		}
		Criteria criteria = detachedCriteria.getExecutableCriteria(getSession());
		criteria.setCacheable(true);
		return criteria.list();
	}

	public List findByCondition(Criterion condition, Class<?> pojoClass, List<QueryColumn> queryCols)
	{
		return hibernateTemplate.findByCriteria(createDetachedCriterion(Arrays.asList(condition), pojoClass, null,
				queryCols, true));
	}

	private DetachedCriteria createDetachedCriterion(List<Criterion> conditions, Class<?> pojoClass,
			List<JoinedTable> joinedTables, List<QueryColumn> queryCols, boolean toObj)
	{
		return createDetachedCriterion(conditions, pojoClass, joinedTables, queryCols, toObj, null);
	}

	public void executeSql(String sql)
	{
		Session session = getSession();
		session.createSQLQuery(sql).executeUpdate();
		session.flush();
	}

	/**
	 * 根据查询条件查询数据
	 * 
	 * @param criterion
	 * @return
	 */
	public List<T> findDataByCondition(Criterion criterion)
	{
		Criteria criteria = getSession().createCriteria(entryClass);
		if (null != criterion)
		{
			criteria.addOrder(Order.desc("id"));
			criteria.add(criterion);
		}
		return criteria.list();
	}

	/**
	 * 根据查询条件查询数据
	 * 
	 * @param criterion
	 * @return
	 */
	public List<T> findDataByConditionAsc(Criterion criterion)
	{
		Criteria criteria = getSession().createCriteria(entryClass);
		if (null != criterion)
		{
			criteria.addOrder(Order.asc("id"));
			criteria.add(criterion);
		}
		return criteria.list();
	}

	/**
	 * 根据查询条件查询数据
	 * 
	 * @param criterion
	 * @return
	 */
	public List<T> findDataByConditionAscString(Criterion criterion, String asc)
	{
		Criteria criteria = getSession().createCriteria(entryClass);
		if (null != criterion)
		{
			criteria.addOrder(Order.asc(asc));
			criteria.add(criterion);
		}
		return criteria.list();
	}

	public List findByConditions(List<Criterion> conditions, Class<?> pojoClass)
	{
		return findByConditions(conditions, pojoClass, null);
	}

	@SuppressWarnings("unchecked")
	public List findByConditionsAndAlias(List<Criterion> conditions, Class pojoClass, List<JoinedTable> joinedTables)
	{
		DetachedCriteria detachedCriteria = createDetachedCriterion(conditions, pojoClass, joinedTables, null);
		return hibernateTemplate.findByCriteria(detachedCriteria);
	}

	@SuppressWarnings("unchecked")
	public List<T> getListByCriteria(final DetachedCriteria detachedCriteria)
	{
		Criteria criteria = detachedCriteria.getExecutableCriteria(getSession());
		criteria.setProjection(null);
		return criteria.list();
	}

	public void deleteByConditions(List<Criterion> conditions, Class<?> pojoClass)
	{
		List<?> entityList = findByConditions(conditions, pojoClass);
		hibernateTemplate.deleteAll(entityList);
	}

	@SuppressWarnings("unchecked")
	public List getDataList(List<Criterion> conditions, List<Order> orders, int startIndex, int size, Class pojoClass,
			List<JoinedTable> joinedTables)
	{
		return getDataList(conditions, orders, startIndex, size, pojoClass, joinedTables, null, null);
	}

	@SuppressWarnings("unchecked")
	public List getDataList(List<Criterion> conditions, List<Order> orders, int startIndex, int size, Class pojoClass,
			List<JoinedTable> joinedTables, List<QueryColumn> queryCols, Class resultClass)
	{
		return getDataList(conditions, orders, startIndex, size, pojoClass, joinedTables, queryCols, resultClass, null);
	}

	@SuppressWarnings("unchecked")
	public List getDataList(List<Criterion> conditions, List<Order> orders, int startIndex, int size, Class pojoClass,
			List<JoinedTable> joinedTables, List<QueryColumn> queryCols, Class resultClass, List<Projection> projections)
	{
		DetachedCriteria detachedCriteria = createDetachedCriterions(conditions, pojoClass, joinedTables, queryCols,
				true, resultClass, projections);
		if (orders != null)
		{
			for (Order order : orders)
			{
				detachedCriteria.addOrder(order);
			}

		}
		return hibernateTemplate.findByCriteria(detachedCriteria, startIndex, size);
	}

	/*
	 * TODO 目前仅支持2层 别名, 如 EncoderChannel.java内，可查询 relation.device的属性，不可查询relation.device.organization的属性
	 * 
	 * @param toObj 只有在queryCols有值的时候才会用到该属性，表示是否将查询结果转化为pojo对象
	 */
	@SuppressWarnings("unchecked")
	private DetachedCriteria createDetachedCriterions(List<Criterion> conditions, Class pojoClass,
			List<JoinedTable> joinedTables, List<QueryColumn> queryCols, boolean toObj, Class resultClass,
			List<Projection> projections)
	{
		QueryMixture queryMixture = new QueryMixture();
		queryMixture.setConditions(conditions);
		queryMixture.setPojoClass(pojoClass);
		queryMixture.setJoinedTables(joinedTables);
		queryMixture.setQueryCols(queryCols);
		queryMixture.setToObj(toObj);
		queryMixture.setResultClass(resultClass);
		queryMixture.setProjections(projections);
		return createDetachedCriterion(queryMixture);
	}

	public Object findSingleRecordByCondtions(List<Criterion> conditions, Class<?> pojoClass)
	{
		return getFirstRecordOrNull(findByConditions(conditions, pojoClass));
	}

	public void saveEntry(Object o)
	{
		getHibernateTemplate().save(o);
	}

	public List<?> findByCondition(Criterion condition, Class<?> pojoClass)
	{
		return hibernateTemplate
				.findByCriteria(createDetachedCriterion(Arrays.asList(condition), pojoClass, null, null));
	}

	public Object deleteHibernateTemplateBySql(final String sql)
	{
		SQLQuery q = getSession().createSQLQuery(sql);
		return q.executeUpdate();
	}

	public Object merge(Object entity)
	{
		return getHibernateTemplate().merge(entity);
	}

	/**
	 * add by haven.qu 批量更新
	 * 
	 * @param collection
	 */
	@SuppressWarnings("unchecked")
	public void saveOrUpdateAll(final Collection collection)
	{
		batchSaveOrUpdate(collection);
	}

	/**
	 * 批量保存
	 * 
	 * @param collection
	 */
	@SuppressWarnings("unchecked")
	public void saveAll(final Collection collection)
	{
		Session session = getSession();
		session.setFlushMode(FlushMode.AUTO);
		for (Object obj : collection)
		{
			session.save(obj);
		}
		session.flush();
	}

	@SuppressWarnings("unchecked")
	public void batchExecute(String sqlTemplate, List<List> dataDuplicatedAttrValues,
			List<List> dataUnduplicateAttrValues)
	{
		batchExecute(sqlTemplate, null, dataDuplicatedAttrValues, dataUnduplicateAttrValues, null);
	}

	@SuppressWarnings({ "deprecation", "unchecked" })
	public void batchExecute(String sqlTemplate, List<List> dataAttrValuesList, List<List> dataDuplicatedAttrValues,
			List<List> dataUnduplicateAttrValues, BigDecimal autoIncreaseValue)
	{
		Session session = getSession();

		PreparedStatement pStatement = null;
		try
		{
			Connection connection = SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
			pStatement = connection.prepareStatement(sqlTemplate);
			final int BATCH_SIZE = 5000;
			int currentBatchSize = 0;
			if (dataAttrValuesList != null)
			{
				for (List currentData : dataAttrValuesList)
				{
					for (int i = 0; i < currentData.size(); i++)
					{
						addParamToPreparedStatement(pStatement, currentData.get(i), i + 1);
					}
					currentBatchSize = addBatch(pStatement, BATCH_SIZE, currentBatchSize);
				}
			}
			else if (dataUnduplicateAttrValues != null)
			{
				for (List unduplicateAttrValues : dataUnduplicateAttrValues)
				{
					if (dataDuplicatedAttrValues != null)
					{
						for (List dataAttrValues : dataDuplicatedAttrValues)
						{
							int index = 0;
							for (Object attrValue : dataAttrValues)
							{
								index++;
								addParamToPreparedStatement(pStatement, attrValue, index);
							}

							for (Object unduplicateAttrValue : unduplicateAttrValues)
							{
								index++;
								addParamToPreparedStatement(pStatement, unduplicateAttrValue, index);
							}

							if (autoIncreaseValue != null)
							{
								index++;
								addParamToPreparedStatement(pStatement, autoIncreaseValue, index);
								autoIncreaseValue = autoIncreaseValue.add(BigDecimal.ONE);
							}

							currentBatchSize = addBatch(pStatement, BATCH_SIZE, currentBatchSize);
						}
					}
				}
			}
			if (currentBatchSize != 0)
			{
				pStatement.executeBatch();
			}
		}
		catch (Exception e)
		{
			throwErrForBatchInsert(sqlTemplate + dataAttrValuesList, pStatement, e);
		}
		finally
		{
			closePreparedStatement(pStatement);
		}
	}

	protected void closePreparedStatement(PreparedStatement pStatement)
	{
		if (pStatement != null)
		{
			try
			{
				pStatement.close();
			}
			catch (SQLException e)
			{
				throw new RuntimeException(e + " at closing preparedStatement");
			}
		}
	}

	protected void throwErrForBatchInsert(String sqlTemplate, PreparedStatement pStatement, Exception e)
	{
		StringBuilder err = new StringBuilder();
		err.append(e).append(" --> ").append(sqlTemplate);
		if (pStatement != null)
		{
			Object sql = EnhancedReflectionUtils.getFieldValue(pStatement, "inner");
			if (sql != null)
			{
				err.append("\n").append(sql);
			}
		}
		throw new RuntimeException(err.toString());
	}

	protected void addParamToPreparedStatement(PreparedStatement pStatement, Object param, int paramIndex)
			throws SQLException
	{
		if (param != null && param.getClass() == Date.class)
		{
			pStatement.setTimestamp(paramIndex, new Timestamp(((Date) param).getTime()));
		}
		else
		{
			pStatement.setObject(paramIndex, param);
		}
	}

	private int addBatch(PreparedStatement pStatement, final int BATCH_SIZE, int currentBatchSize) throws SQLException
	{
		pStatement.addBatch();
		currentBatchSize++;
		if (currentBatchSize == BATCH_SIZE)
		{
			pStatement.executeBatch();
			currentBatchSize = 0;
		}
		return currentBatchSize;
	}

	public List findByCondition(Criterion condition, Class<?> pojoClass, List<QueryColumn> queryCols, boolean toObj)
	{
		return hibernateTemplate.findByCriteria(createDetachedCriterion(Arrays.asList(condition), pojoClass, null,
				queryCols, toObj));
	}

	/**
	 * add by haven.qu
	 * 
	 * @param sql
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Object getListBySql(final String sql)
	{
		return getSession().createSQLQuery(sql).list();
	}

	public void delete(DbTableForDataDelete dbTable)
	{
		executeSql(dbTable.toSql());
	}

	@SuppressWarnings("unchecked")
	public List findByConditions(List<Criterion> conditions, Class<?> pojoClass, List<QueryColumn> queryCols,
			boolean toObj, List<Order> orders)
	{
		return hibernateTemplate.findByCriteria(createDetachedCriterion(conditions, pojoClass, null, queryCols, toObj,
				orders));
	}

	@SuppressWarnings("deprecation")
	public void batchExecuteSql(List<String> sqls)
	{
		Statement statement = null;
		try
		{
			Connection connection = SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
			statement = connection.createStatement();
			for (String sql : sqls)
			{
				statement.addBatch(sql);
			}
			statement.executeBatch();
		}
		catch (Exception e)
		{
			throw new RuntimeException(e);
		}
		finally
		{
			if (statement != null)
			{
				try
				{
					statement.close();
				}
				catch (SQLException e)
				{
					throw new RuntimeException(e + " when closing statement");
				}
			}
		}
	}

	@SuppressWarnings("unchecked")
	public List findByConditionsAndAlias(List<Criterion> conditions, Class pojoClass, List<JoinedTable> joinedTables,
			List<QueryColumn> queryCols)
	{
		DetachedCriteria detachedCriteria = createDetachedCriterion(conditions, pojoClass, joinedTables, queryCols);
		return hibernateTemplate.findByCriteria(detachedCriteria);
	}

	public void refresh(Object entity)
	{
		getHibernateTemplate().refresh(entity);
	}

	public void deleteAll(final Collection collection)
	{
		hibernateTemplate.deleteAll(collection);
	}

	@SuppressWarnings("unchecked")
	public List getDataList(List<Criterion> conditions, List<Order> orders, int startIndex, int size, Class pojoClass,
			List<JoinedTable> joinedTables, List<QueryColumn> queryCols)
	{
		return getDataList(conditions, orders, startIndex, size, pojoClass, joinedTables, queryCols, null);
	}

	public Integer deleteExpiredLog(String table, String column, final Date beforeDate)
	{
		final String hql = "delete " + table + " where " + column + " < :beforeDate";
		return getSession().createQuery(hql).setDate("beforeDate", beforeDate).executeUpdate();
	}

	public void flushHibernateTemplateSession()
	{
		hibernateTemplate.flush();
	}
}
